package com.tool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

public class DatabaseTool {
	public static final String JDBC_URL = "jdbc:mysql://localhost:3306/Luke?characterEncoding=UTF-8";
	public static final String JDBC_USER = "root";
	public static final String JDBC_PWD = "123456";

	// 建立数据库连接
	private static Connection getConncetion() {
		Connection cn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			cn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PWD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cn;
	}

	public static int doUpdate(String sql) {
		int result = 0;
		Connection cn = getConncetion();
		if (cn == null) {
			return 0;
		}

		Statement st = null;
		try {
			st = cn.createStatement();
			result = st.executeUpdate(sql);
		} catch (SQLException e) {
			//e.printStackTrace();
			JOptionPane.showMessageDialog(null, "更新失败，请检查输入的值","错误", JOptionPane.ERROR_MESSAGE);
		} finally {
			closeConnection(cn, st);
		}
		return result;
	}

	/**	查询语句
	 * @param sql	输入的sql语句
	 * @return	返回List集合 其中的每一条String代表一条记录，字段之间用“，”隔开。
	 */
	public static List<String> doQuery(String sql) {
		ResultSet result = null; // 查询结果
		Connection cn = getConncetion(); // 打开数据库连接
		if (cn == null) {
			return null;
		}
		List<String> list = new ArrayList<>(); // 集合
		Statement st = null;
		String s = null; // 总字符串
		try {
			st = cn.createStatement();
			result = st.executeQuery(sql); // 查询
			ResultSetMetaData colInfo = result.getMetaData();
			while (result.next()) {
				int colCount = colInfo.getColumnCount();
				String str = ""; // 每一行数据
				for (int i = 1; i <= colCount; i++) {
					if (i != colCount) {
						str += result.getString(i) + ",";
					} else {
						str += result.getString(i);
					}
				}
				list.add(str);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(cn, st);
		}
		return list;
	}
	/**插入
	 * @param sql
	 * @return 
	 */
	public static int doInsert(String sql){
		int res = 0;
		Connection cn = getConncetion();
		if (cn == null) {
			return 0;
		}
		Statement st = null;	//用于插入语句
		try {
			st = cn.createStatement();
			res = st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(cn, st);
		}
		return res;
	}

	public static int doDelete(String sql){
		int res = 0;
		Connection cn = getConncetion();
		if (cn == null) {
			return 0;	//返回
		}
		Statement st = null;
		try {
			st = cn.createStatement();
			res = st.executeUpdate(sql);	//插入
		} catch (SQLException e) {
			//e.printStackTrace();
			JOptionPane.showMessageDialog(null, "请选择表格中的值","警告", JOptionPane.WARNING_MESSAGE);
		} finally {
			closeConnection(cn, st);
		}
		return res;
	}
	
	/**	查询数据表中的所有记录总数
	 * @param bookname 根据书名查询
	 * @return 返回总数
	 * @throws SQLException	数据库异常
	 */
	public static int getNum(String booksql) throws SQLException
	{
		int num = 0;
		Connection cn=getConncetion();
		Statement st = cn.createStatement();
		ResultSet result = st.executeQuery(booksql);
		while(result.next())
		{
			 num=result.getInt("count(*)");
		}
		return num;
	}
	
	private static void closeConnection(Connection cn, Statement st) {
		try {
			st.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}
